﻿using System.Collections.Generic;

namespace SimplyCleverMiddleTier
{
    public static partial class CountryProvider
    {
        public static KeyValuePair[] GetUSStates()
        {
            List<KeyValuePair> states = new List<KeyValuePair>(100);

            Db.ReadFrom("SimplyClever", false,
                command =>
                {
                    command.CommandText = "SELECT c.Code AS CountryCode, cs.Code AS StateCode, cs.Name AS StateName FROM CountryStates cs JOIN Countries c ON cs.CountryId=c.Id WHERE c.Code='US' ORDER BY cs.Code";
                },
                reader =>
                {
                    states.Add(new KeyValuePair() { Key = reader.GetValue<string>("StateCode"), Value = reader.GetValue<string>("StateName") });

                });

            return states.ToArray();
        }
    }
}
